package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewStaffStatisticsDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewStaffStatistics;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewStaffStatisticsDaoImp extends GenericDao<ViewStaffStatistics> implements IViewStaffStatisticsDao {


    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {
        String id = (String)paramHashMap.get("id");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String type = (String)paramHashMap.get("type");
        String startTime_begin = (String)paramHashMap.get("startTime_begin");
        String endTime_end = (String)paramHashMap.get("endTime_end");

        String idSql = "";
        String adminUserIdSql = "";
        String startTime_beginSql = "";
        String endTime_endSql = "";
        String lessonsClassTime_begin = "";
        String lessonsClassTime_end = "";
        if(id!=null && !id.equals("")){
            idSql = "\tAND sys_admin_user.id = '"+id+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            adminUserIdSql = "\tAND sys_admin_user.id in ('"+adminUserId+"')\n";
        }
        if(startTime_begin!=null && !startTime_begin.equals("")){
            startTime_beginSql = "\t\t\t\t\t\tAND unix_timestamp(tss_member_package.createTime) >= unix_timestamp('"+startTime_begin+"')\n";
            lessonsClassTime_begin = "\t\t\t\t\tAND unix_timestamp(tss_class_lessons.classTime) >= unix_timestamp('"+startTime_begin+"')\n";
        }
        if(endTime_end!=null && !endTime_end.equals("")){
            endTime_endSql = "\t\t\t\t\t\tAND unix_timestamp(tss_member_package.createTime) <= unix_timestamp('"+endTime_end+"')\n";
            lessonsClassTime_end = "\t\t\t\t\tAND unix_timestamp(tss_class_lessons.classTime) <= unix_timestamp('"+endTime_end+"')\n";
        }

        String sql = "SELECT\n" +
                "\tJ.*,\n" +
                "\tIFNULL(K.sumListen,0) AS sumListen,\n" +
                "\tIFNULL(K.teachingLessons,0) AS teachingLessons,\n" +
                "\tIFNULL(K.teachingHours,0) AS teachingHours,\n" +
                "\tIFNULL(K.shouldAttend,0) AS shouldAttend,\n" +
                "\tIFNULL(K.sumAttendance,0) AS sumAttendance,\n" +
                "\tIFNULL(K.consumptionHours,0) AS consumptionHours,\n" +
                "\tIFNULL(K.ConsumptionRightResponsibilitys,0) AS ConsumptionRightResponsibilitys\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tsys_admin_user.id,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\tFROM\n" +
                "\t\t\tsys_admin_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tsys_admin_user.post = 6\n" +
                "\t\t\tFIND_IN_SET( 6,sys_admin_user.partTime) \n" +
                adminUserIdSql +
                idSql +
                "\t\tGROUP BY\n" +
                "\t\t\tsys_admin_user.id\n" +
                "\t) J\n" +
                "LEFT JOIN(\n" +
                "\tSELECT \n" +
                "\t\tI.*,\n" +
                "\t\tIFNULL(SUM(I.expendClassHours),0) AS consumptionHours,\n" +
                "\t\tIFNULL(SUM(I.ConsumptionRightResponsibility),0) AS ConsumptionRightResponsibilitys\n" +
                "\tFROM (\n" +
                "\t\tSELECT \n" +
                "\t\t\tG.*,\n" +
                "\t\t\ttss_classes.expendClassHour,\n" +
                "\t\t\ttss_member_package.nowUnitPrice,\n" +
                "\t\t\tIFNULL(SUM(tss_classes.expendClassHour),0) AS expendClassHours,\n" +
                "\t\t\tIFNULL(SUM(tss_classes.expendClassHour*tss_member_package.nowUnitPrice),0) AS ConsumptionRightResponsibility\n" +
                "\t\tFROM (\n" +
                "\t\t\tSELECT \n" +
                "\t\t\t\tE.*,\n" +
                "\t\t\t\tCOUNT(tss_member_attend_class_record.id) AS shouldAttend,\n" +
                "\t\t\t\tCOUNT(if(tss_member_check_attendance.attendenceStatus=1,true,null)) AS sumAttendance\n" +
                "\t\t\tFROM (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tC.*,\n" +
                "\t\t\t\t\tCOUNT(C.id) AS teachingLessons,\n" +
                "\t\t\t\t\tIFNULL(SUM(tss_classes.expendClassHour),0) AS teachingHours\n" +
                "\t\t\t\tFROM(\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tA.*,\n" +
                "\t\t\t\t\t\tCOUNT(B.id) AS sumListen\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\tsys_admin_user.id,\n" +
                "\t\t\t\t\t\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\tsys_admin_user\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\tsys_admin_user.post = 6\n" +
                "\t\t\t\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\t\t\t\tsys_admin_user.id\n" +
                "\t\t\t\t\t\t) A\n" +
                "\t\t\t\t\tLEFT JOIN (\n" +
                "\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t*\n" +
                "\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\ttss_member_package\n" +
                "\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t1 = 1\n" +
                startTime_beginSql +
                endTime_endSql +
                "\t\t\t\t\t) B ON A.id = B.teacherId\n" +
                "\t\t\t\t\tWHERE B.type = "+type+"\n" +
                "\t\t\t\t\tGROUP BY A.id\n" +
                "\t\t\t\t)C\n" +
                "\t\t\t\tLEFT JOIN (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t*\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\ttss_class_lessons\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\ttss_class_lessons.`status` = 1\n" +
                lessonsClassTime_begin +
                lessonsClassTime_end +
                "\t\t\t\t)D ON D.adminUserId = C.id\n" +
                "\t\t\t\tLEFT JOIN tss_classes\n" +
                "\t\t\t\tON D.classId = tss_classes.id\n" +
                "\t\t\t\tWHERE tss_classes.type = "+type+"\n" +
                "\t\t\t\tGROUP BY C.id\n" +
                "\t\t\t)E\n" +
                "\t\t\tLEFT JOIN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t*\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\ttss_class_lessons\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\ttss_class_lessons.`status` = 1\n" +
                lessonsClassTime_begin +
                lessonsClassTime_end +
                "\t\t\t)F \n" +
                "\t\t\tON F.adminUserId = E.id\n" +
                "\t\t\tLEFT JOIN tss_classes ON F.classId = tss_classes.id\n" +
                "\t\t\tLEFT JOIN tss_member_attend_class_record\n" +
                "\t\t\tON F.id = tss_member_attend_class_record.lessonsId\n" +
                "\t\t\tLEFT JOIN tss_member_check_attendance\n" +
                "\t\t\tON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "\t\t\tWHERE tss_classes.type = "+type+"\n" +
                "\t\t\tGROUP BY E.id\n" +
                "\t\t\t)G\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\t*\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\ttss_class_lessons\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\ttss_class_lessons.`status` = 1\n" +
                lessonsClassTime_end +
                lessonsClassTime_end +
                "\t\t)H \n" +
                "\t\tON H.adminUserId = G.id\n" +
                "\t\tLEFT JOIN tss_classes ON H.classId = tss_classes.id\n" +
                "\t\tLEFT JOIN tss_member_attend_class_record\n" +
                "\t\tON H.id = tss_member_attend_class_record.lessonsId\n" +
                "\t\tLEFT JOIN tss_member_check_attendance\n" +
                "\t\tON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "\t\tLEFT JOIN tss_member_package\n" +
                "\t\tON tss_member_package.id = tss_member_attend_class_record.memberPackageId\n" +
                "\t\tWHERE tss_classes.type = "+type+"\n" +
                "\t\tAND tss_member_check_attendance.attendenceStatus=1\n" +
                "\t\tGROUP BY tss_member_check_attendance.id\n" +
                "\t)I\n" +
                "\tGROUP BY I.id\n" +
                ")K ON J.id = K.id";
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewStaffStatistics> populate = BeanUtils.populate(ViewStaffStatistics.class, (List<Map<String, Object>>) pageBean.getList(), "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }
}
